package movee.manager.core;

import lombok.extern.slf4j.Slf4j;
import movee.domain.mapper.BackupDeviceMapper;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * 将设备历史备份记录转移到新的表中
 *
 * @author movee
 */
@Service
@Slf4j
public class AppDeviceDataMigration {

    private static final String DEVICE_TABLE_NAME = "t_backup_device";

    private final BackupDeviceMapper deviceMapper;
    private final JdbcOperations jdbcTemplate;

    private final DateTimeFormatter monthFormatter = DateTimeFormatter.ofPattern("yyyyMM");
    private final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    public AppDeviceDataMigration(BackupDeviceMapper deviceMapper,
                                  JdbcOperations jdbcTemplate) {

        this.deviceMapper = deviceMapper;
        this.jdbcTemplate = jdbcTemplate;
    }

    @Transactional(rollbackFor = {Exception.class, Error.class})
    public boolean migrateHistoryBackupDeviceData() {

        boolean migration = false;

        // 当天
        LocalDate localDate = LocalDateTime.now().toLocalDate();

        // 7天
        Timestamp historyPoint = Timestamp.valueOf(localDate.minusDays(6L).atStartOfDay());

        // 表中最老的备份记录
        Timestamp oldestCreateTime = deviceMapper.findDeviceTaskOldestCreateTime();
        if (oldestCreateTime.before(historyPoint)) {
            // 存在7天之前的备份记录

            // 每个月一个表
            String historyTableName = DEVICE_TABLE_NAME + "_" +
                    monthFormatter.format(oldestCreateTime.toLocalDateTime());
            log.debug("=====oldestCreateTime: {}, historyTableName: {}",
                    formatter.format(oldestCreateTime.toLocalDateTime()), historyTableName);
            if (!checkTableIfExisted(historyTableName)) {
                // 如果表不存在，则创建一个
                createTable(historyTableName);
            }

            // 将备份记录转移到历史表中
            migrateDeviceData(historyTableName, oldestCreateTime.toLocalDateTime().toLocalDate());

            migration = true;
        }

        return migration;
    }

    private boolean checkTableIfExisted(String tableName) {
        String sql = String.format("show tables like '%s'", tableName);
        return jdbcTemplate.query(sql, rs -> (Boolean) rs.first());
    }

    private void createTable(String tableName) {
        String sql = String.format("create table if not exists `%s` like `%s`", tableName, DEVICE_TABLE_NAME);
        jdbcTemplate.execute(sql);
    }

    private void migrateDeviceData(String tableName, LocalDate historyDate) {

        String startOfDay = formatter.format(historyDate.atStartOfDay());
        String nextStartOfDay = formatter.format(historyDate.plusDays(1L).atStartOfDay());

        String sql = String.format("insert into `%s` select * from `%s` " +
                "where `create_time` >= '%s' and `create_time` < '%s' limit 1000",
                tableName, DEVICE_TABLE_NAME, startOfDay, nextStartOfDay);
        int copyRows = jdbcTemplate.update(sql);

        sql = String.format("delete from `%s` where `create_time` >= '%s' and `create_time` < '%s' limit 1000",
                DEVICE_TABLE_NAME, startOfDay, nextStartOfDay);

        int delRows = jdbcTemplate.update(sql);

        log.debug("=====copyRows: {}, delRows: {}", copyRows, delRows);
    }
}
